﻿using System;
using System.Collections.Generic;
using System.Text;
using DbSetupHelper.Common;
using DbSetupHelper.Common.Exceptions;
using DbSetupHelper.Plugin.ScriptPackage;
using System.Collections.ObjectModel;
using System.Xml.XPath;
using System.Reflection;
using System.Data.SqlClient;
using System.IO;

namespace DbSetupHelper.Business
{
    /// <summary>
    /// Class used to assist with running the database scripts against the provided database.
    /// </summary>
    static class DatabaseBuilder
    {
        /// <summary>
        /// Occurs when [on message].
        /// </summary>
        public static event EventHandler<MessageEventArgs> OnMessage;

        /// <summary>
        /// Runs the database builder with the supplied install parameters.
        /// </summary>
        /// <param name="installParameters">The install parameters.</param>
        public static void Run(InstallParameters installParameters)
        {
            if (installParameters == null)
                throw new ArgumentNullException("installParameters");
            if (installParameters.ScriptPackageAssembly == null)
                throw new ArgumentNullException("installParameters.ScriptPackageAssembly");

            //off off and away.
            RunScriptPattern(installParameters);
        }

        //TODO: Allow for multiple providers. (MSSQL, Oracle, etc...)
        private static void RunScriptPattern(InstallParameters installParms)
        {
            //create needed objects.
            SqlConnection conn = new SqlConnection();
            StringBuilder connString = new StringBuilder();
            SqlCommand command = new SqlCommand();

            //build the connection string
            connString.Append("Data Source=");
            connString.Append(installParms.Server.Replace("%computername%", Environment.MachineName));
            connString.Append(";Initial Catalog=master;User Id=");
            connString.Append(installParms.User);
            connString.Append(";Password=");
            connString.Append(installParms.Password);
            connString.Append(";");

            //set up connection and open the connection
            conn.ConnectionString = connString.ToString();
            conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
            command.Connection = conn;

            try
            {
                conn.Open();
            }
            catch (SqlException)
            {
                Console.Out.WriteLine("Could not connect to SQL instance. Please check the connection string. ");
                Console.Out.WriteLine("\"" + conn.ConnectionString + "\"");
            }

            if (conn.State != System.Data.ConnectionState.Closed)
            {
                try
                {
                    foreach (DBScript script in installParms.ScriptPattern)
                    {
                        bool runScript = false;

                        //figure out if we should run the script or not.
                        if (installParms.InteractiveInstall)
                        {
                            //we asked the user, it's their choice.
                            runScript = !script.Disabled;
                        }
                        else
                        {
                            //if the script is disabled, don't run it.
                            //else, run the script unless we're not in test install mode and it
                            //is a test install script
                            if (script.Disabled)
                                runScript = false;
                            else
                                runScript = !script.TestInstall || installParms.TestInstall;
                        }

                        if (runScript)
                        {
                            //TODO: Handle failures inside of scripts.
                            IEnumerable<string> commands = ExtractCommands(script.ScriptBody);
                            foreach (string commandText in commands)
                            {
                                if (!string.IsNullOrEmpty(commandText))
                                {
                                    
                                    command.CommandText = commandText;
                                    command.ExecuteNonQuery();
                                }
                            }

                            if (OnMessage != null)
                            {
                                OnMessage(null, new MessageEventArgs(script.ScriptFileName + " completed successfully."));
                            }

                        }
                    }
                }
                finally
                {
                    command.Dispose();
                    conn.Close();
                }
            }
        }
        private static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Console.WriteLine(e.Message);
        }
        private static IEnumerable<string> ExtractCommands(string sqlBlock)
        {
            //Remove line comments
            string pattern = @"--.*" + Environment.NewLine;
            string replace = Environment.NewLine;
            System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(pattern);
            sqlBlock = reg.Replace(sqlBlock, replace);
            //Remove block comments
            pattern = @"(/\*).*(\*/)";
            reg = new System.Text.RegularExpressions.Regex(pattern);
            sqlBlock = reg.Replace(sqlBlock, replace);

            //Split string into an Array by line
            string[] seperators = new string[] { Environment.NewLine };
            string[] lines = sqlBlock.Split(seperators, StringSplitOptions.None);

            Queue<string> queue = new Queue<string>();

            //Go line by line
            StringBuilder buffer = new StringBuilder();
            foreach (string line in lines)
            {
                //Look for GO on a line with either a space behind it or endline
                pattern = @"^GO( *|" + Environment.NewLine + ")";
                System.Text.RegularExpressions.Regex rgx =
                    new System.Text.RegularExpressions.Regex(pattern, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                System.Text.RegularExpressions.Match m = rgx.Match(line);
                if (m.Success)
                {
                    queue.Enqueue(buffer.ToString());
                    buffer = new StringBuilder();
                }
                else
                {
                    buffer.Append(line);
                    buffer.Append(Environment.NewLine);
                }
            }

            return queue;
        }
    }
}
